const Database = require('better-sqlite3');
const path = require('path');

// Connect to the database
const dbPath = path.join(__dirname, 'data', 'delivery-management.db');
const db = new Database(dbPath, { readonly: true });

console.log('=== DEBUGGING EMBEDDED FIELDS ISSUE ===\n');

// 1. Check parameter field metadata for table_c2
console.log('1. PARAMETER FIELD METADATA FOR table_c2:');
const c2Fields = db.prepare(`
  SELECT id, field_name, field_column 
  FROM parameter_field_metadata 
  WHERE table_name = 'table_c2' 
  ORDER BY field_sequence
`).all();

console.log(`Found ${c2Fields.length} fields in table_c2 metadata:`);
c2Fields.forEach(field => {
  console.log(`  ID: ${field.id}, field_name: "${field.field_name}", field_column: "${field.field_column}"`);
});

// 2. Check the actual schema of a device instance table
console.log('\n\n2. ACTUAL SCHEMA OF device_instances_piping_centrifugal_pump:');
const schema = db.prepare(`PRAGMA table_info(device_instances_piping_centrifugal_pump)`).all();
const namingFields = schema.filter(col => col.name.startsWith('naming_'));
const positionFields = schema.filter(col => col.name.startsWith('position_'));
const baseParamsFields = schema.filter(col => col.name.startsWith('base_params_'));

console.log(`Naming fields (${namingFields.length}):`);
namingFields.forEach(field => console.log(`  - ${field.name}: ${field.type}`));

console.log(`\nPosition fields (${positionFields.length}):`);
positionFields.forEach(field => console.log(`  - ${field.name}: ${field.type}`));

console.log(`\nBase params fields (${baseParamsFields.length}):`);
baseParamsFields.forEach(field => console.log(`  - ${field.name}: ${field.type}`));

// 3. Check device type mappings
console.log('\n\n3. DEVICE TYPE MAPPINGS FOR CENTRIFUGAL PUMP:');
const mapping = db.prepare(`
  SELECT * FROM device_type_mappings 
  WHERE english_name = 'centrifugal_pump'
`).get();

if (mapping) {
  console.log('Mapping found:');
  console.log(`  classification_code: ${mapping.classification_code}`);
  console.log(`  naming_table: ${mapping.naming_table}`);
  console.log(`  position_table: ${mapping.position_table}`);
  console.log(`  base_params_table: ${mapping.base_params_table}`);
} else {
  console.log('No mapping found for centrifugal_pump');
}

// 4. Check field metadata for the mapped tables
if (mapping) {
  console.log('\n\n4. FIELD METADATA FOR MAPPED TABLES:');
  
  [mapping.naming_table, mapping.position_table, mapping.base_params_table].forEach(tableName => {
    if (tableName) {
      const fields = db.prepare(`
        SELECT id, field_name, field_column 
        FROM parameter_field_metadata 
        WHERE table_name = ?
        ORDER BY field_sequence
      `).all(tableName);
      
      console.log(`\n${tableName} (${fields.length} fields):`);
      fields.forEach(field => {
        console.log(`  ID: ${field.id}, field_name: "${field.field_name}", field_column: "${field.field_column}"`);
      });
    }
  });
}

// 5. Check if there are any parameter records in instance tables
console.log('\n\n5. SAMPLE PARAMETER DATA IN INSTANCE TABLES:');
const sampleData = db.prepare(`
  SELECT * FROM device_instances_piping_centrifugal_pump 
  WHERE naming_设计物资编码 IS NOT NULL OR position_设计设备位号_管线号 IS NOT NULL 
  LIMIT 1
`).get();

if (sampleData) {
  console.log('Found sample data with parameters:');
  console.log('Naming fields:');
  Object.keys(sampleData).filter(key => key.startsWith('naming_')).forEach(key => {
    if (sampleData[key]) console.log(`  ${key}: ${sampleData[key]}`);
  });
  console.log('Position fields:');
  Object.keys(sampleData).filter(key => key.startsWith('position_')).forEach(key => {
    if (sampleData[key]) console.log(`  ${key}: ${sampleData[key]}`);
  });
} else {
  console.log('No parameter data found in device instances');
}

db.close();
console.log('\n=== DEBUG COMPLETE ===');